#!/usr/bin/python3
import os
import time
import json
from datetime import datetime, timedelta 
import math
import os
from tablestore import *
from tablestore import OTSClient


class AliTableClient():

    def __init__(self, end_point_url: str, instance_name: str):
        self.__client = OTSClient(end_point=end_point_url, access_key_id="LTAI5tSrZGSgmRqSRMvSCWvU", access_key_secret="nelWYd9cdSbs1baLtxUpIQn9M0imnS", instance_name=instance_name)


    # 获取所有表格名称
    def get_all_table_name(self) -> []:
        table_name_ary = self.__client.list_table()
        return table_name_ary
        pass

    # 创建表格
    # 主键需要自增的话 使用：PK_AUTO_INCR [('gid', 'INTEGER'), ('uid', 'INTEGER', PK_AUTO_INCR)]
    def create_table_if_not_exist(self, table_name:str, primary_key:[], columns:[]):
        exist_table_name_ary = self.get_all_table_name()
        if table_name in exist_table_name_ary:
            return

        table_meta = TableMeta(table_name, primary_key, columns)

        # 创建TableOptions，数据永不过期，只保存1个版本数据。
        table_option = TableOptions(-1, 1)

        # 设置预留读吞吐量为0，预留写吞吐量为0。
        reserved_throughput = ReservedThroughput(CapacityUnit(0, 0))

        # # 设置二级索引的索引名称、主键列和属性列。索引类型默认为全局二级索引。
        # secondary_indexes = [
  #         SecondaryIndexMeta('index1', ['i', 's'], ['bool', 'b', 'd']),
  #     ]

        self.__client.create_table(table_meta, table_option, reserved_throughput)

        # 创建多元索引
        # 注意这个一旦创建，会导致预留读吞吐量，会造成每日扣费，目前为 40cu，一天扣费 0.48.
        # self.create_search_indexes(table_name, primary_key, columns)
        pass

    # 创建多元索引，为了提高数据表的查询
    def create_search_indexes(self, table_name:str, primary_key, columns):

        fields = []
        for primary in primary_key:
            field_type = FieldType.KEYWORD
            if primary[1] == "STRING":
                field_type = FieldType.KEYWORD
            elif primary[1] == "INTEGER":
                field_type = FieldType.LONG

            field = FieldSchema(primary[0], field_type, index=True, enable_sort_and_agg=True, store=False)
            fields.append(field)

        for column in columns:
            field_type = FieldType.KEYWORD
            if column[1] == "STRING":
                field_type = FieldType.KEYWORD
            elif primary[1] == "INTEGER":
                field_type = FieldType.LONG
            field = FieldSchema(column[0], field_type, index=True, enable_sort_and_agg=True, store=False)
            fields.append(field)
            pass

        # print(fields)

        # index_sort = Sort(sorters=[PrimaryKeySort(SortOrder.ASC),FieldSort(SortOrder.ASC)])
        index_sort = None
        index_meta = SearchIndexMeta(fields, index_sort=index_sort)
        index_name = "index_"+table_name
        self.__client.create_search_index(table_name, index_name, index_meta)
        pass

    # 删除表格
    def delete_table(self, table_name:str):
        try:
            self.__client.delete_table(table_name)
            return True
        except Exception as e:
            print(e)
            return False
    # 写入一行数据   主键需要自增的话 PK_AUTO_INCR ：primary_key = [('gid',1), ('uid', PK_AUTO_INCR)]
    # 如果主键自增，想要写入数据后，获取主键的值，return_type = ReturnType.RT_PK
    # 如果主键相同，则会更新这一行的数据
    def write_one_row(self, table_name:str, primary:dict, columns:dict={}, condition:Condition = None, return_type = None):
        row_dict = {
                'primary':primary,
                'columns':columns
            }
        row = self.convert_row(row_dict)
        return self.write_one_by_row(table_name, row, condition, return_type)
        pass

    # 写入一行数据   主键需要自增的话 PK_AUTO_INCR ：primary_key = [('gid',1), ('uid', PK_AUTO_INCR)]
    # 如果主键自增，想要写入数据后，获取主键的值，return_type = ReturnType.RT_PK
    # 如果主键相同，则会更新这一行的数据
    def write_one_by_row(self, table_name:str, row:Row, condition:Condition = None, return_type = None):
        try:

            # 获取更新, 如果没有指定ReturnType，则return_row为None。
            consumed, return_row = self.__client.put_row(table_name, row, return_type = return_type)
            # print ('Write succeed, consume %s write cu.' % consumed.write)
            # print ('Primary key:%s' % return_row.primary_key)
            if return_row is not None:
                return True, return_row.primary_key
            else:
                return True, None
        # 客户端异常，一般为参数错误或者网络异常。
        except OTSClientError as e:
            print("write_one_row failed, http_status:%d, error_message:%s" % (e.get_http_status(), e.get_error_message()))
            return  False, None
        # 服务端异常，一般为参数错误或者流控错误。
        except OTSServiceError as e:
            print("write_one_row failed, http_status:%d, error_code:%s, error_message:%s, request_id:%s" % (e.get_http_status(), e.get_error_code(), e.get_error_message(), e.get_request_id()))   
            return False, None
        pass
    
    # 更新一行数据
    def update_one_row(self,table_name:str,  primary:dict, columns:dict={}, condition:Condition = Condition(RowExistenceExpectation.EXPECT_EXIST), return_type = None):
        row_dict = {
                'primary':primary,
                'operation':'put',
                'columns':columns
            }
        row = self.convert_row(row_dict)
        return self.update_one_by_row(table_name, row, condition, return_type)

    # 更新一行数据
    def update_one_by_row(self,table_name:str, row:Row, condition:Condition, return_type = None):
        try:

            # 获取更新, 如果没有指定ReturnType，则return_row为None。
            consumed, return_row = self.__client.update_row(table_name, row, condition)
            # print ('Write succeed, consume %s write cu.' % consumed.write)
            # print ('Primary key:%s' % return_row.primary_key)
            if return_row is not None:
                return True, return_row.primary_key
            else:
                return True, None
        # 客户端异常，一般为参数错误或者网络异常。
        except OTSClientError as e:
            print(e)
            print("update_one_row failed, http_status:%d, error_message:%s" % (e.get_http_status(), e.get_error_message()))
            return  False, None
        # 服务端异常，一般为参数错误或者流控错误。
        except OTSServiceError as e:
            print("update_one_row failed, http_status:%d, error_code:%s, error_message:%s, request_id:%s" % (e.get_http_status(), e.get_error_code(), e.get_error_message(), e.get_request_id()))   
            return False, None
        pass        
    
        # 删除一行数据
    def delete_one_row(self, table_name:str,  primary:dict, columns:dict={}, condition:Condition = None, return_type = None):
        row_dict = {
            'primary':primary,
            'columns':columns
        }
        row = self.convert_row(row_dict)
        return self.delete_one_by_row(table_name, row, condition, return_type)
    # 删除一行数据
    def delete_one_by_row(self, table_name:str, row:Row, condition:Condition = None, return_type = None):
        try:

            # 获取更新, 如果没有指定ReturnType，则return_row为None。
            consumed, return_row = self.__client.delete_row(table_name, row, condition)
            # print ('Write succeed, consume %s write cu.' % consumed.write)
            # print ('Primary key:%s' % return_row.primary_key)
            if return_row is not None:
                return True, return_row.primary_key
            else:
                return True, None
        # 客户端异常，一般为参数错误或者网络异常。
        except OTSClientError as e:
            print("delete_one_row failed, http_status:%d, error_message:%s" % (e.get_http_status(), e.get_error_message()))
            return  False, None
        # 服务端异常，一般为参数错误或者流控错误。
        except OTSServiceError as e:
            print("delete_one_row failed, http_status:%d, error_code:%s, error_message:%s, request_id:%s" % (e.get_http_status(), e.get_error_code(), e.get_error_message(), e.get_request_id()))   
            return False, None
        pass    

    # primary_key 是主键的dict，例如 {'id':xsxxxs,'uuid':xsdasdsads}
    # columns_name_to_get 是list 获取列的名称 ，例如：['age','name']
    # 查询一行数据，必须要完全获取的到主键的值，即使是 自增的主键，也就虚获取自增主键的值。
    def get_one_row(self, table_name: str, primary_key:{}, columns_name_to_get:[] = None, column_filter=None, cond:Condition=None):
        try:
            row = {}
            primary = []
            for key in primary_key:
                primary.append((key,primary_key[key]))
            # 获取更新, 如果没有指定ReturnType，则return_row为None。
            consumed, return_row, next_token = self.__client.get_row(table_name, primary, columns_name_to_get, cond, 1)
            # print('Read succeed, consume %s read cu.' % consumed.read)
            # print('Value of primary key: %s' % return_row.primary_key)
            # print('Value of attribute: %s' % return_row.attribute_columns)

            if return_row is None:
                return None

            primary_info = {}
            for primary in return_row.primary_key:
                primary_info[primary[0]] = primary[1]
                pass

            column_info = {}
            for attr in return_row.attribute_columns:
                column_info[attr[0]] = attr[1]
            
            row = {
                "primary":primary_info,
                "columns":column_info
            }
            return row
        # 客户端异常，一般为参数错误或者网络异常。
        except OTSClientError as e:
            print("get_one_row failed, http_status:%d, error_message:%s" % (e.get_http_status(), e.get_error_message()))
            return None
        # 服务端异常，一般为参数错误或者流控错误。
        except OTSServiceError as e:
            print("get_one_row failed, http_status:%d, error_code:%s, error_message:%s, request_id:%s" % (e.get_http_status(), e.get_error_code(), e.get_error_message(), e.get_request_id()))   
            return None
        pass            

    def get_range_row(self, table_name, inclusive_start_primary_key, exclusive_end_primary_key):
        try:
            row_ary = []
            # 查询所有列。
            columns_to_get = []

            # 每次最多返回90行，如果总共有150个结果，首次查询时指定limit=100，则第一次最多返回90，最少可能返回0个结果，但是next_start_primary_key不为None。
            limit = 50

            consumed, next_start_primary_key, row_list, next_token = self.__client.get_range(table_name,
                Direction.FORWARD,inclusive_start_primary_key,exclusive_end_primary_key,columns_to_get,limit)
            all_rows = []
            all_rows.extend(row_list)

            while next_start_primary_key is not None:
                inclusive_start_primary_key = next_start_primary_key
                consumed, next_start_primary_key, row_list, next_token = client.get_range(table_name,
                Direction.FORWARD,inclusive_start_primary_key,exclusive_end_primary_key,columns_to_get,limit)

                all_rows.extend(row_list)

            # print(len(all_rows))
            # print(all_rows[0])

            for row in all_rows:
                # print(row.primary_key, row.attribute_columns)
                primary_info = {}
                for primary in row.primary_key:
                    primary_info[primary[0]] = primary[1]
                    pass
                column_info = {}
                for attr in row.attribute_columns:
                    column_info[attr[0]] = attr[1]
                    pass

                row_ary.append({
                    "primary":primary_info,
                    "columns":column_info
                })
                
            return row_ary
        # 客户端异常，一般为参数错误或者网络异常。
        except OTSClientError as e:
            print('get row failed, http_status:%d, error_message:%s' % (e.get_http_status(), e.get_error_message()))
            return []
        # 服务端异常，一般为参数错误或者流控错误。
        except OTSServiceError as e:
            print('get row failed, http_status:%d, error_code:%s, error_message:%s, request_id:%s' % (e.get_http_status(), e.get_error_code(), e.get_error_message(), e.get_request_id()))
            return []
        pass

    # 转化 row Obj
    def convert_row(self, obj_dict):
        primary_info = obj_dict["primary"]
        column_info = obj_dict["columns"]
        operation = None
        if "operation" in obj_dict:
            operation = obj_dict["operation"]

        primarys = []
        for key in primary_info:
            primarys.append((key, primary_info[key]))

        if operation is None or len(operation) <= 0:
            columns = []
            for key in column_info:
                columns.append([key, column_info[key]])
            row = Row(primarys,columns)
            return row
        else:
            info_struct = {}
            columns = []
            for key in column_info:
                columns.append(tuple([key, column_info[key]]))
            info_struct[operation] = columns
            row = Row(primarys,info_struct)
            return row





if __name__ == '__main__':
    # table_client = AliTableClient("https://IAPVS.cn-hangzhou.vpc.tablestore.aliyuncs.com","IAPVS")
    table_client = AliTableClient("https://pdfconvert.cn-shanghai.ots.aliyuncs.com","pdfconvert")
    # table_client = AliTableClient("https://pdfconvert.cn-hangzhou.vpc.tablestore.aliyuncs.com","pdfconvert")

    table_name_ary = table_client.get_all_table_name()
    print(table_name_ary)

    # ary = transaction_info_ary = table_client.get_range_row('maxntfs',
 #        [('uuid','J2N6GKM209'),('transaction_id', '')],
 #        [('uuid','J2N6GKM209'),('transaction_id', INF_MAX)])
    # print(ary)

    # 删除 table
    # table_client.delete_table("test")

    # 创建table
    # table_client.create_table_if_not_exist("test",
 #        [('uuid','STRING'),#序列号 唯一标识
 #        ('id', 'INTEGER', PK_AUTO_INCR)], # id 自增主键
        
 #        [('modify_date', 'STRING'), # 最新修改的时间
 #        ('product_id','STRING'), # 购买的ipa项目
 #        ('original_transaction_id','STRING'), # 交易的原始订单
 #        ('pay_status','STRING'), # 支付状态，主要是通过Apple Store API 验证的
 #        ('purchase_date_ms', 'STRING'), # 购买的时间
 #        ('expires_day', 'STRING'), # 失效的天数 
 #        ('environment','STRING'), # 环境
 #        ('status','STRING'), # 是否处于有效状态
 #        ('currency','STRING'), # 货币
 #        ('price','STRING'), # 价格
 #        ('other','STRING') # 其他
 #    ])


    # # 写入数据
    # # 如果主键存在，则会删除之前的行，重新写入，
    # table_client.write_one_row(
    #   table_name="test",
    #   primary={
    #       "uuid":'781231123',
    #       "id":PK_AUTO_INCR
    #   },
    #   columns={
    #       'modify_date':'2024-05-10 14:20:18',
    #       'product_id': 'com.andrew.labs.wallpaper.free'
    #   })

    # 写入数据
    # 如果主键存在，则会删除之前的行，重新写入，
    # table_client.write_one_by_row("test",
    #   Row(
    #       [('uuid','KHA67123a13'),
    #         ('id', PK_AUTO_INCR)], # id 自增主键
            
    #         [('modify_date', '2024-05-10 14:20:18'), # 最新修改的时间
    #         ('product_id','com.andrew.labs.wallpaper.free'), # 购买的ipa项目
    #         ('original_transaction_id','400001321266708'), # 交易的原始订单
    #         ('pay_status','pay'), # 支付状态，主要是通过Apple Store API 验证的
    #         ('purchase_date_ms', '1688526346000'), # 购买的时间
    #         ('expires_day', '0'), # 失效的天数 
    #         ('environment','Product'), # 环境
    #         ('status','valid'), # 是否处于有效状态
    #         ('currency','CNY'), # 货币
    #         ('price','68.00'), # 价格
    #         ('other','') # 其他
    #       ])
    #   )
    

    # 更新数据
    # table_client.update_one_row("test",primary = {"uuid":"781231123","id":1721211968120000},columns = {"currency":"RMB","environment":"Sandbox"})


    # 更新数据
    # row = Row([('uuid','781231123'),("id",1721211968120000)],{
    #     "put":[('currency','1231')]
    # })
    # table_client.update_one_by_row("test",row,Condition(RowExistenceExpectation.IGNORE))


    
    # table_client.update_one_row("test",{"uuid":"781231123","id":1721211968120000},{"currency":"USD"})

    
    # 查询一行的数据
    # row = table_client.get_one_row("test", {'uuid':'6712KHA67123','id':1715946889334000})
    # print(row)

    # 查询一行的数据,只要想要的columes
    # row = table_client.get_one_row("test", {'uuid':'6712KHA67123','id':1715946889334000}, columns_name_to_get=['product_id','status'])
    # print(row)


    # 删除数据
    # table_client.delete_one_row("test", {"uuid":'6712KHA67123','id':1718260347262000})
  

    # # 删除数据
    # table_client.delete_one_by_row("test",       
    #   Row([('uuid','12sds3hagjasdjk'),('id',1718260347262000)],
    #       [])
    #   )







